/**
 * title: TrackedFeatureDAO.java
 * description: Program that provides abstract interface to the database with the
 *              help of the TrackedFeature model, which encapsulates tracked feature related items.
 * date: December 28 2013
 * id: 2540434
 * course: COMP 495 (Project-Server)
 * @author Mohamed B Thaha
 * @version 1.0
 * @copyright 2013 Mohamed B Thaha
 */

/**
 * DOCUMENTATION...
 */

/**                                                                               
 *
 *<H1>TrackedFeatureDAO</H1>
 *
 *<H3>Purpose and Description</H3>
 *
 *<P>
 * An application that allows the job of the DAO (data access object) to provide 
 * an abstract interface to the database.
 *</P>
 *<P>
 * The DAOs main purpose is to perform CRUD operations on the database, which basically 
 * means create, retrieve, update, and delete data by virtue of a database connection or 
 * JDBC API driver.
 *</P>
 *<P>
 * This program requires the Sun Java SDK version 1.7 or better.
 *</P>
 *                                                                              
 *<DL>
 *<DT> Compiling and running instructions</DT>
 *<DT> Assuming SDK 1.7 (or later) and the CLASSPATH are set up properly.</DT>
 *<DT> Assuming the use of Eclipse IDE (Kepler) 4.3.1.</DT>
 *<DT> Change to the directory containing the source code.</DT>
 *<DD> Compile:    TrackedFeatureDAO.java</DD>
 *<DD> Run:        TrackedFeatureDAO.java</DD>
 *<DD> Document:   Generate Javadoc TrackedFeatureDAO</DD>
 *</DL>
 */

/**
 *
 * <H3>Classes</H3>
 *
 *<P>
 * public class TrackedFeatureDAO {<BR>
 * This is the main public class for this application. It contains all the
 * methods required to perform CRUD operations.
 *</P>
 *
 * <H3>TrackedFeatureDAO Methods</H3>
 *
 *<P>
   public static boolean create(TrackedFeature trackedFeature, long deviceID, long userID) throws Exception {<BR>
   This method returns either a true value indicating that a feature information has been
   registered into the database or a false value indicating that the insertion
   has failed.
 *</P>
 * 
 *<P>
   public static TrackedFeature[] select(Hashtable<String, Object> selectedFeaturesFilters) throws Exception {<BR>
   This method returns an array list of tracked features if such exists. The method also coincides
   with the FeatureSelection select method which retrieves an array of feature selections.
 *</P>
 *
 *<P>
   public static boolean update(long deviceID, long userID, TrackedFeature currentFeatureName, Hashtable<String, Object> args) throws Exception {<BR>
   This method returns either a true value indicating that the update was a success 
   or a false value indicating that the update has failed.
 *</P> 
 *
 *<P>
   public static boolean deleteAllTF(long deviceID, long userID) throws Exception {<BR>
   This method returns either a true value indicating that all registers 
   with the specified device id and user id has been deleted or a false value indicating 
   that the operation has failed.
 *</P>
 *
 *<P>
   public static boolean deleteOneFeature(long deviceID, long userID, TrackedFeature oneFeature) throws Exception {<BR>
   This method returns either a true value indicating a single feature name corresponding 
   to the specified device id and user id has been deleted or a false value indicating that the 
   operation has failed.
 *</P> 
 * 
 * <H3>TrackedFeatureDAO Instance Variables (Tags - Constants)</H3>
 *
 *<P>
 * public static final String FEATURE_NAME = "name"
 * public static final String USAGE = "usage"
 * public static final String DEVICE_ID = "device_id"
 * public static final String USER_ID = "user_id"
 * public static final String START_USING = "start_time"
 * public static final String END_USING = "end_time"
 *</P>
 */

/**
 * CODE...
 */

package athabasca.ca.dao;
/** Java core packages */
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Enumeration;
import java.util.Hashtable;

import athabasca.ca.model.FeatureUsage;
import athabasca.ca.model.TrackedFeature;
import athabasca.ca.util.*;
/** primary (public) class for TrackedFeatureDAO */
public class TrackedFeatureDAO {
	public static final String FEATURE_NAME = "name";
	public static final String USAGE = "usage";
	public static final String DEVICE_ID = "device_id";
	public static final String USER_ID = "user_id";
	public static final String START_USING = "start_time";
	public static final String END_USING = "end_time";
	
	/** 
	 * Method's purpose is to insert feature information into the database
	 * @param feature
	 * device feature name
	 * @param usageTime
	 * usage time of the feature
	 * @param deviceID
	 * device identification number
	 * @param userID
	 * user identification number
	 * @return
	 * boolean
	 * @throws Exception
	 */
	public static boolean create(TrackedFeature trackedFeature, long deviceID, long userID) throws Exception {
		Connection conn = null;
		PreparedStatement query = null;
		boolean isTrue = false;
		
		try {
			// A 'safeguard' that makes sure the feature name is not empty 
			// otherwise do not proceed to execute the rest of the code.
			if ((trackedFeature.getName() == null) || (deviceID <= 0) || (userID <= 0))
				return false;
			else {
			
			conn = postgreSQL.postgreSQLConn();
			
			// This query will only insert data into the selected_features table.
			query = conn.prepareStatement("INSERT INTO selected_features " + 
					"VALUES (?, ?, ?, ?, ?, ?)");
			query.setObject(1, trackedFeature.getName());
			query.setObject(2, trackedFeature.getTotalUsage());
            query.setObject(3, deviceID);
            query.setObject(4, userID);
            query.setObject(5, trackedFeature.getMostRecentFeatureUsage().getStartUsing());
            if (trackedFeature.getMostRecentFeatureUsage().getEndUsing().after(trackedFeature.getMostRecentFeatureUsage().getStartUsing()))
            	query.setObject(6, trackedFeature.getMostRecentFeatureUsage().getEndUsing());
            else {
            	return false;
            }
        	int insertTFUsage = query.executeUpdate();
			query.close();
			
			// We must make sure that data was inserted in the selected_features
    		// table.
			if (insertTFUsage > 0) {
				isTrue = true;
			}
			}
			
		} catch (ArrayIndexOutOfBoundsException e) {
			e.getMessage();
		} catch (NullPointerException e) {
			e.getMessage();
		} catch (SQLException sqlError) {
			sqlError.getMessage();
		} catch (Exception e) {
			e.getMessage();
			e.printStackTrace();
		} finally {
			if (conn != null) conn.close();
		}
		
		return isTrue;
	}

	/** 
	 * Method's purpose is to retrieve an array list of selected tracked features
	 * @param selectedFeaturesFilters
	 * hash table
	 * @return
	 * ArrayList<TrackedFeature>
	 * @throws Exception
	 */
	public static TrackedFeature[] select(Hashtable<String, Object> selectedFeaturesFilters) throws Exception {
		PreparedStatement query = null;
		Connection conn = null;
		
		ArrayList<TrackedFeature> selectedFeaturesList = new ArrayList<TrackedFeature>();
		
		try {
			// If the filters contained in the hash table are empty, return a null value 
			// and do not proceed to execute the rest of the code.
			if (selectedFeaturesFilters.isEmpty())
				return null;
			else {
			
			TrackedFeature selectedFeature;
			
			String sql = "";
			String key = "";
			String statement = "";
			String statement2 = "";
			
			Enumeration<String> keys = selectedFeaturesFilters.keys( );
			
			// Build the 'statement' on the fly.
			while( keys.hasMoreElements( ) ) {
				key = keys.nextElement( );
				
				statement2 = "selected_features.";
				
				/* [RENAN]
				 * I forgot to mention the the feature_name is also a key, so, maybe,
				 * it could be added just like the other keys
				 * [/RENAN]
				 * */
				if ((key != DEVICE_ID) || (key != USER_ID))
					statement = statement + statement2 + key + " = '" + selectedFeaturesFilters.get(key) + "'";
				else
					statement = statement + statement2 + key + " = " + selectedFeaturesFilters.get(key);
				
				if (keys.hasMoreElements())
					statement = statement + " AND ";
			}
			
			sql = ("SELECT *" +
					" FROM selected_features" +
					" WHERE " + statement);
			
			conn = postgreSQL.postgreSQLConn();
			
			// This query selects all data from the selected_features table.
			query = conn.prepareStatement(sql);
			
			ResultSet results = query.executeQuery();
			
			// If the results returned from the database was empty, return a null value 
			// and there is not reason to continue to execute the rest of the code.
			if (results.wasNull())
				return null;
			else {
			
			while (results.next()) {
				selectedFeature = new TrackedFeature(results.getString(FEATURE_NAME));
				
				FeatureUsage usage = new FeatureUsage(results.getFloat(USAGE));
				usage.setStartUsing(results.getDate(START_USING));
				usage.setEndUsing(results.getDate(END_USING));
				
				selectedFeature.updateUsageHistory(usage);
				
				selectedFeaturesList.add(selectedFeature);
			}
			}
			
			query.close();
			}
		} catch (ArrayIndexOutOfBoundsException e) {
        	System.err.println ( e.getMessage( ) );
            e.printStackTrace ( );     
        } catch (NullPointerException e) {
        	System.err.println ( e.getMessage( ) );
        	e.printStackTrace ( );
        } catch (SQLException sqlError) {
        	System.err.println ( sqlError.getMessage( ) );
        	sqlError.printStackTrace ( );
        } catch (Exception e) {
        	System.err.println ( e.getMessage( ) );
        	e.printStackTrace();
        } finally {
        	if (conn != null) conn.close();
        }
		
		if (selectedFeaturesList.size() > 0)
	    	return (TrackedFeature[]) selectedFeaturesList.toArray(new TrackedFeature[selectedFeaturesList.size()]);
	    else
	    	return null;
		
	}
	
	/** 
	 * Method's purpose is to update tracked feature related information based on the provided hash 
	 * table filters
	 * @param deviceID
	 * device identification number
	 * @param currentFeatureName
	 * the feature name to be updated
	 * @param args
	 * hash table
	 * @return
	 * Boolean
	 * @throws Exception
	 */
	public static boolean update(long deviceID, long userID, TrackedFeature currentFeatureName, Hashtable<String, Object> args) throws Exception {
		Connection conn = null;
		PreparedStatement query = null;
        boolean isTrue = false;
        
        try {
        	// If the filters contained in the hash table are empty or the current feature name is empty, return a null value 
        	// and do not proceed to execute the rest of the code.
        	if ((currentFeatureName.getName() == null) || (args.isEmpty()) || (deviceID <= 0))
        		return false;
        	else {
        	
        	String sql = "";
        	String key;
        	key = null;
        	float usage;
        	Date start;
        	Date end;
        	
        	conn = postgreSQL.postgreSQLConn();
        	
        	Enumeration<String> keys = args.keys( );
        	
        	// Build the 'statement' on the fly.
        	while (keys.hasMoreElements()) {
        		key = keys.nextElement();
            	
            	sql = ("UPDATE selected_features"
            			+ " SET " + key + " = ?"
                        + " WHERE device_id = " + deviceID
                        + " AND user_id = " + userID
                        + " AND name = " + "'" + currentFeatureName.getName() + "'");
            	
            	switch (key) {
            		case USAGE:
            		{
            			usage = (float) args.get(USAGE);
            			query = conn.prepareStatement(sql);
            			query.setObject(1, usage);
            			int update = query.executeUpdate();
            			query.close();
            			if (update > 0)
            				isTrue = true;
            			break;
            		}
            		case START_USING:
            		{
            			start = (Date) args.get(START_USING);
                    	query = conn.prepareStatement(sql);
                    	query.setObject(1, start);
                    	int update = query.executeUpdate();
                    	query.close();
                    	// We must make sure that data was updated in the device usage
                		// table.
                    	if (update > 0)
                    		isTrue = true;
                    	break;
            		}
            		case END_USING:
            		{
            			end = (Date) args.get(END_USING);
            			if (end.before((Date) args.get(START_USING))) {
            				isTrue = false;
            				break;
            			}
                    	query = conn.prepareStatement(sql);
                    	query.setObject(1, end);
                    	int update = query.executeUpdate();
                    	query.close();
                    	if (update > 0)
                    		isTrue = true;
                    	break;
            		}
            		default:
            			isTrue = false;
            			return isTrue;
            	}
        	}
        	}
        } catch (ArrayIndexOutOfBoundsException e) {
        	System.err.println ( e.getMessage( ) );
            e.printStackTrace ( );  
            isTrue = false;
        } catch (NullPointerException e) {
        	System.err.println ( e.getMessage( ) );
        	e.printStackTrace ( );
        	isTrue = false;
        } catch (SQLException sqlError) {
        	System.err.println ( sqlError.getMessage( ) );
        	sqlError.printStackTrace ( );
        	isTrue = false;
        } catch (Exception e) {
        	System.err.println ( e.getMessage( ) );
        	e.printStackTrace();
        	isTrue = false;
        } finally {
        	if (conn != null) conn.close();
        }
        return isTrue;
	}
	
	/** 
	 * Method's purpose is to delete all tracked feature name information tied 
	 * to the specified device id
	 * @param deviceID
	 * device identification number
	 * @return
	 * boolean
	 * @throws Exception
	 */
	public static boolean deleteAllTF(long deviceID, long userID) throws Exception {
		Connection conn = null;
		PreparedStatement query = null;
        boolean isTrue = false;
        
        try {
        	if (deviceID <= 0)
        		return false;
        	else {
        	conn = postgreSQL.postgreSQLConn();
            	
        	query = conn.prepareStatement("DELETE FROM selected_features"
        			+ " WHERE device_id = ?"
        			+ " AND user_id = ?");
            query.setObject(1, deviceID);
            query.setObject(2, userID);
        	int deleteTF = query.executeUpdate();
            query.close();
            
            // We must make sure that data was deleted from the selected_features
    		// table.
            if (deleteTF > 0) {
            	isTrue = true;
            }
        	}
        	
        } catch (ArrayIndexOutOfBoundsException e) {
        	System.err.println ( e.getMessage( ) );
            e.printStackTrace ( );    
            isTrue = false;
        } catch (NullPointerException e) {
        	System.err.println ( e.getMessage( ) );
        	e.printStackTrace ( );
        	isTrue = false;
        } catch (SQLException sqlError) {
        	System.err.println ( sqlError.getMessage( ) );
        	sqlError.printStackTrace ( );
        	isTrue = false;
        } catch (Exception e) {
        	System.err.println ( e.getMessage( ) );
        	e.printStackTrace();
        	isTrue = false;
        } finally {
        	if (conn != null) conn.close();
        }
        
        return isTrue;
	}
	
	/** 
	 * Method's purpose is to delete a single tracked feature name tied to the specified 
	 * device id
	 * @param deviceID
	 * device identification number
	 * @param oneFeature
	 * the one feature to be deleted
	 * @return
	 * boolean
	 * @throws Exception
	 */
	public static boolean deleteOneFeature(long deviceID, long userID, TrackedFeature oneFeature) throws Exception {
		Connection conn = null;
		PreparedStatement query = null;
        boolean isTrue = false;
        
        try {
        	// A 'safeguard' that makes sure the tracked feature name is not empty 
        	// otherwise do not proceed to execute the rest of the code.
        	if ((oneFeature.getName() == null) || (deviceID <= 0))
        		return false;
        	else {
        	
        	conn = postgreSQL.postgreSQLConn();
            	
        	query = conn.prepareStatement("DELETE FROM selected_features"
        			+ " WHERE device_id = ?"
        			+ " AND user_id = ?"
        			+ " AND name = ?");
            query.setObject(1, deviceID);
            query.setObject(2, userID);
            query.setObject(3, oneFeature.getName());
        	int deleteATF = query.executeUpdate();
            query.close();
            
            // We must make sure that data was deleted from the selected_features
    		// table.
            if (deleteATF > 0) {
            	isTrue = true;
            }
        	}
        	
        } catch (ArrayIndexOutOfBoundsException e) {
        	System.err.println ( e.getMessage( ) );
            e.printStackTrace ( );    
            isTrue = false;
        } catch (NullPointerException e) {
        	System.err.println ( e.getMessage( ) );
        	e.printStackTrace ( );
        	isTrue = false;
        } catch (SQLException sqlError) {
        	System.err.println ( sqlError.getMessage( ) );
        	sqlError.printStackTrace ( );
        	isTrue = false;
        } catch (Exception e) {
        	System.err.println ( e.getMessage( ) );
        	e.printStackTrace();
        	isTrue = false;
        } finally {
        	if (conn != null) conn.close();
        }
        return isTrue;
	}
} // End of public class TrackedFeatureDAO